﻿Imports MySql.Data.MySqlClient
Imports Microsoft.Office.Interop.Excel

Public Class GroupReport4_1
    Public respone As Object
    Public CountCourseID As Integer = 0

    Public myCountCourseID As Integer = 0
    Public SQL As MySqlConnection

    Public Sub New(ByRef SQLConnection As MySqlConnection)
        InitializeComponent()
        SQL = SQLConnection
    End Sub

    ''  Private Sub GroupReport4_1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
    ''Dim mainForm As main = New main()
    ''  mainForm.Show()
    'Me.Close()
    '' End Sub

    Private Sub GroupReport4_1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader


        If SQL.State = ConnectionState.Closed Then
            SQL.Open()
        End If


        'mySqlCommand.CommandText = "SELECT DISTINCT * from Employee where dept_id in (SELECT dept_id from Department where dept_name like" + " '%" + TextBox1.Text + "%' )" + " or emp_name like " + "'%" + TextBox1.Text + "%'" + " or emp_surname like " + "'%" + TextBox1.Text + "%'" + " or emp_position like " + "'%" + TextBox1.Text + "%'" + " or emp_level like " + "'%" + TextBox1.Text + "%'" + ";"
        mySqlCommand.CommandText = "SELECT DISTINCT comp_name FROM Department ;"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = SQL
        mySqlAdaptor.SelectCommand = mySqlCommand
        mySqlReader = mySqlCommand.ExecuteReader

        ComboBox1.Items.Clear()

        While (mySqlReader.Read())


            '  With ListView1.Items.Add(mySqlReader("emp_code"))
            ' .subItems.add(mySqlReader("emp_name") + " " + mySqlReader("emp_surname"))
            ' .subItems.add(mySqlReader("dept_name"))
            ' .subItems.add(mySqlReader("dev_name"))
            ' .subItems.add(mySqlReader("emp_level"))
            ' End With
            ComboBox1.Items.Add(mySqlReader("comp_name"))

        End While

        SQL.Close()

    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        FolderBrowserDialog1.Description = "Pick Folder to store Excecl files"
        FolderBrowserDialog1.ShowNewFolderButton = True
        FolderBrowserDialog1.SelectedPath = "C:\"
        FolderBrowserDialog1.ShowDialog()

        TextBox1.Text = FolderBrowserDialog1.SelectedPath

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim mainform As main = New main()
        main.Show()
        Me.Close()
    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader


        If SQL.State = ConnectionState.Closed Then
            SQL.Open()
        End If


        'mySqlCommand.CommandText = "SELECT DISTINCT * from Employee where dept_id in (SELECT dept_id from Department where dept_name like" + " '%" + TextBox1.Text + "%' )" + " or emp_name like " + "'%" + TextBox1.Text + "%'" + " or emp_surname like " + "'%" + TextBox1.Text + "%'" + " or emp_position like " + "'%" + TextBox1.Text + "%'" + " or emp_level like " + "'%" + TextBox1.Text + "%'" + ";"
        mySqlCommand.CommandText = "SELECT DISTINCT dev_name FROM Department where comp_name = '" & ComboBox1.SelectedItem.ToString() & "' ;"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = SQL
        mySqlAdaptor.SelectCommand = mySqlCommand
        mySqlReader = mySqlCommand.ExecuteReader

        ComboBox2.Items.Clear()

        While (mySqlReader.Read())


            '  With ListView1.Items.Add(mySqlReader("emp_code"))
            ' .subItems.add(mySqlReader("emp_name") + " " + mySqlReader("emp_surname"))
            ' .subItems.add(mySqlReader("dept_name"))
            ' .subItems.add(mySqlReader("dev_name"))
            ' .subItems.add(mySqlReader("emp_level"))
            ' End With
            ComboBox2.Items.Add(mySqlReader("dev_name"))

        End While

        SQL.Close()
    End Sub

    Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged
        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader


        If SQL.State = ConnectionState.Closed Then
            SQL.Open()
        End If


        'mySqlCommand.CommandText = "SELECT DISTINCT * from Employee where dept_id in (SELECT dept_id from Department where dept_name like" + " '%" + TextBox1.Text + "%' )" + " or emp_name like " + "'%" + TextBox1.Text + "%'" + " or emp_surname like " + "'%" + TextBox1.Text + "%'" + " or emp_position like " + "'%" + TextBox1.Text + "%'" + " or emp_level like " + "'%" + TextBox1.Text + "%'" + ";"
        mySqlCommand.CommandText = "SELECT DISTINCT * FROM Department where dev_name = '" & ComboBox2.SelectedItem.ToString() & "' ;"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = SQL
        mySqlAdaptor.SelectCommand = mySqlCommand
        mySqlReader = mySqlCommand.ExecuteReader

        ListView1.Items.Clear()

        While (mySqlReader.Read())


            With ListView1.Items.Add(mySqlReader("dept_id"))
                .subItems.add(mySqlReader("dept_name"))
            End With

        End While

        SQL.Close()
    End Sub



    '=======start comment


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        respone = MsgBox("Are you sure to generate report", MsgBoxStyle.OkCancel + MsgBoxStyle.Information, "Warning Messsage")
        If respone = 1 Then
            Dim mySqlCommand As New MySqlCommand
            Dim mySqlAdaptor As New MySqlDataAdapter
            Dim mySqlReader As MySqlDataReader
            System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
            Dim Counter_Dept As Integer = 0
            Dim EmpCode_Storage As New ArrayList
            Dim CurEmployee As String

            Dim Counter_Emp As Integer = 0
            Dim Counter_Row As Integer = 0
            Dim Counter_Row1 As Integer = 0

            Dim oExcel As Object
            Dim oBook As Object
            Dim oSheet As Object

            Dim start_date As String


            If ListView1.CheckedItems.Count <> 0 Then
                While Counter_Dept < ListView1.CheckedItems.Count

                    If SQL.State = ConnectionState.Closed Then
                        SQL.Open()
                    End If

                    mySqlCommand.CommandText = "SELECT * FROM Employee JOIN Department where Department.dept_id = '" & ListView1.CheckedItems(Counter_Dept).Text & "' and Employee.dept_id = Department.dept_id ;"
                    'MsgBox(mySqlCommand.CommandText)

                    mySqlCommand.Connection = SQL
                    mySqlAdaptor.SelectCommand = mySqlCommand
                    mySqlReader = mySqlCommand.ExecuteReader

                    While (mySqlReader.Read())

                        '                    EmpCode_Storage.Add(mySqlReader("Employee.emp_code"))
                        EmpCode_Storage.Add(mySqlReader("emp_code"))
                        'MsgBox(mySqlReader("emp_code"))
                    End While

                    SQL.Close()

                    'Me.Close()

                    Counter_Dept = Counter_Dept + 1
                End While


                While Counter_Emp < EmpCode_Storage.Count


                    'EACH EMPLOYEE
                    CurEmployee = EmpCode_Storage.Item(Counter_Emp)

                    'Start a new workbook in Excel
                    'หนึ่งคนงาน หนึ่งเวริคบุ๊ค
                    oExcel = CreateObject("Excel.Application")
                    oBook = oExcel.Workbooks.Add

                    oSheet = oBook.Worksheets(1)

                    'Write to first sheet Header
                    With oSheet
                        .Range("A1:N50").Font.Name = "Angsana New"
                        .Range("H:H").ColumnWidth = 0.5
                        .Range("B1:N100").Font.Bold = True
                        With .Range("H1")
                            .ColumnWidth = 10
                            .Font.Bold = True
                            .Font.Size = 20
                        End With
                        With .Range("C2:M4")
                            .Font.Bold = True
                        End With
                        With .Range("A2:N50")
                            .Font.Size = 14
                        End With

                    End With
                    If SQL.State = ConnectionState.Closed Then
                        SQL.Open()
                    End If

                    mySqlCommand.CommandText = "SELECT * FROM Employee JOIN Department where Employee.emp_code = '" & CurEmployee & "' and Employee.dept_id = Department.dept_id ;"

                    mySqlCommand.Connection = SQL
                    mySqlAdaptor.SelectCommand = mySqlCommand
                    mySqlReader = mySqlCommand.ExecuteReader

                    For J = 7 To 10
                        oSheet.Range("B7:G7").Borders(J).Weight = 2 ' xlThin
                    Next
                    For J = 7 To 10
                        oSheet.Range("B8:D8").Borders(J).Weight = 2 ' xlThin
                    Next
                    For J = 7 To 10
                        oSheet.Range("C8:C8").Borders(J).Weight = 2 ' xlThin
                    Next
                    For J = 7 To 10
                        oSheet.Range("D8:D8").Borders(J).Weight = 2 ' xlThin
                    Next
                    For J = 7 To 10
                        oSheet.Range("E8:E8").Borders(J).Weight = 2 ' xlThin
                    Next
                    For J = 7 To 10
                        oSheet.Range("F8:F8").Borders(J).Weight = 2 ' xlThin
                    Next

                    For J = 7 To 10
                        oSheet.Range("G8:G8").Borders(J).Weight = 2 ' xlThin
                    Next
                    oSheet.Range("A:A").ColumnWidth = 1.5
                    oSheet.Range("B:B").ColumnWidth = 14
                    oSheet.Range("C:C").ColumnWidth = 14
                    oSheet.Range("D:D").ColumnWidth = 8.5
                    oSheet.Range("E:E").ColumnWidth = 8.5
                    oSheet.Range("F:F").ColumnWidth = 4
                    oSheet.Range("G:G").ColumnWidth = 8.5
                    oSheet.Range("H:H").ColumnWidth = 1.5
                    oSheet.Range("I:I").ColumnWidth = 14
                    oSheet.Range("J:J").ColumnWidth = 14
                    oSheet.Range("K:K").ColumnWidth = 8.5
                    oSheet.Range("L:L").ColumnWidth = 8.5
                    oSheet.Range("M:M").ColumnWidth = 4
                    oSheet.Range("N:N").ColumnWidth = 8.5
                    ' oSheet.Range("F8:F100").NumberFormat = "00.00"

                    'oSheet.Range("G8:G100").NumberFormat = "#,##0.00"

                    'oSheet.Columns(4).ColumnWidth = 10 'ปรับขนาดคอลัมป์
                    'oSheet.Columns(5).ColumnWidth = 10
                    'oSheet.Columns(6).ColumnWidth = 10

                    'oSheet.Columns(11).ColumnWidth = 10

                    While (mySqlReader.Read())


                        oSheet.Range("H1").Value = "Training Record"

                        oSheet.Range("C2").Value = "Name : "
                        oSheet.Range("C2").Value += mySqlReader("emp_gender") & " " & mySqlReader("emp_name") & " " & mySqlReader("emp_surname")
                        oSheet.Range("I2").Value = "Code : "
                        oSheet.Range("I2").Value += mySqlReader("emp_code")
                        oSheet.Range("C3").Value = "Position : "
                        oSheet.Range("C3").Value += mySqlReader("emp_position")
                        start_date = mySqlReader("emp_start_date")
                        oSheet.Range("K3").Value = "Company : "
                        oSheet.Range("K3").Value += ComboBox1.Text

                        oSheet.Range("E3").Value = "Department : "
                        oSheet.Range("E3").Value += mySqlReader("dept_name")
                        oSheet.Range("I3").Value = "Division:"
                        oSheet.Range("I3").Value += mySqlReader("dev_name")
                        oSheet.Range("K2").Value = "Level : "
                        oSheet.Range("K2").Value += mySqlReader("emp_level")
                        oSheet.Range("C4").Value = "Thai ID : "
                        Try
                            oSheet.Range("C4").Value += mySqlReader("emp_thai_id")
                        Catch ex As Exception
                            oSheet.Range("C4").Value += " "
                        End Try

                        oSheet.Range("C5").Value = "Training Start Date : "
                        oSheet.Range("C5").Value += DateTimePicker1.Value.Date

                        oSheet.Range("I5").Value = "Training End Date : "
                        oSheet.Range("I5").Value += DateTimePicker2.Value.Date


                        oSheet.Range("D7").Value = "On the job"

                        oSheet.Range("B8").Value = "Course"
                        oSheet.Range("C8").Value = "Trainer"
                        oSheet.Range("D8").Value = "Date"
                        oSheet.Range("E8").Value = "Period"
                        oSheet.Range("F8").Value = "Hrs."
                        oSheet.Range("G8").Value = "Cost"

                    End While
                    Dim split_start() As String
                    split_start = Split(start_date, "/")
                    Dim start_year As Integer
                    Dim start_month As Integer
                    Dim now_year As Integer
                    Dim now_month As Integer
                    Dim total As Integer

                    Dim txt_month As String
                    Dim txt_year As String

                    Dim count_money_string As String
                    Dim count_money_int As Integer = 0

                    start_year = CInt(split_start(2))
                    start_month = CInt(split_start(1))

                    now_year = CInt(Date.Now.Year.ToString)
                    now_month = CInt(Date.Now.Month.ToString)

                    total = 12 * (now_year - start_year) + now_month

                    txt_month = (total - start_month) Mod 12
                    txt_year = ((total - start_month) - ((total - start_month) Mod 12)) / 12
                    If start_year > 2100 Then
                        total = 12 * (now_year - start_year + 543) + now_month

                        txt_month = (total - start_month) Mod 12
                        txt_year = ((total - start_month) - ((total - start_month) Mod 12)) / 12


                    Else
                        total = 12 * (now_year - start_year) + now_month

                        txt_month = (total - start_month) Mod 12
                        txt_year = ((total - start_month) - ((total - start_month) Mod 12)) / 12

                    End If






                    oSheet.Range("K4").Value() = "Work Period : " & txt_year & " Years " & txt_month & " Month"
                    oSheet.Range("I4").Value() = "Start Date : " + start_date
                    SQL.Close()



                    'Write to first Sheet Content
                    If SQL.State = ConnectionState.Closed Then
                        SQL.Open()
                    End If

                    mySqlCommand.CommandText = "SELECT * FROM Employee JOIN Department JOIN Training JOIN Course where Employee.emp_code = '" & CurEmployee & "' and Employee.dept_id = Department.dept_id and Employee.emp_code = Training.emp_code and Training.course_id = Course.course_id and training.training_type = 'On the job' and Training.training_date >= '" & DateTimePicker1.Value.Year & "-" & DateTimePicker1.Value.Month & "-" & DateTimePicker1.Value.Day & "' and Training.training_date <= '" & DateTimePicker2.Value.Year & "-" & DateTimePicker2.Value.Month & "-" & DateTimePicker2.Value.Day & "';"

                    mySqlCommand.Connection = SQL
                    mySqlAdaptor.SelectCommand = mySqlCommand
                    mySqlReader = mySqlCommand.ExecuteReader

                    Counter_Row = 9
                    Dim count_on As Integer = 0
                    Dim string_on(1000) As String
                    string_on(0) = "."
                    While (mySqlReader.Read())

                        oSheet.Range("B" & Counter_Row.ToString()).Value = mySqlReader("course_name")
                        oSheet.Range("C" & Counter_Row.ToString()).Value = mySqlReader("course_trainer")
                        oSheet.Range("D" & Counter_Row.ToString()).Value = mySqlReader("training_date")
                        oSheet.Range("E" & Counter_Row.ToString()).Value = mySqlReader("time_start") & " - " & mySqlReader("time_end")
                        oSheet.Range("F" & Counter_Row.ToString()).NumberFormat = "00.00"
                        string_on(count_on) = mySqlReader("training_hours")
                        oSheet.Range("F" & Counter_Row.ToString()).Value = string_on(count_on)
                        oSheet.Range("G" & Counter_Row.ToString()).NumberFormat = "#,##0.00"
                        count_money_string = mySqlReader("training_cost_person")
                        count_money_int += CInt(count_money_string)
                        oSheet.Range("G" & Counter_Row.ToString()).Value = count_money_string
                        Counter_Row = Counter_Row + 1
                        count_on = count_on + 1
                    End While

                    Dim total_h As Integer = 0
                    Dim total_min As Integer = 0
                    Dim total_h_mod_string As String
                    Dim total_min_mod_string As String

                    If string_on(0) <> "." Then
                        For J = 0 To count_on - 1
                            Dim split_total() As String
                            split_total = Split(string_on(J), ".")

                            Dim int_h_hr As Integer = CInt(split_total(0))
                            Dim int_h_min As Integer = CInt(split_total(1))

                            total_h = total_h + int_h_hr
                            total_min = total_min + int_h_min

                        Next

                        Dim total_h_mod As Integer = total_min \ 60
                        Dim total_min_mod As Integer = total_min Mod 60



                        total_h = total_h + total_h_mod

                        If (Len(total_h) < 2) Then
                            total_h_mod_string = "0" + total_h
                        Else
                            total_h_mod_string = total_h
                        End If

                        If (Len(total_min_mod) < 2) Then
                            total_min_mod_string = "0" + total_min_mod
                        Else
                            total_min_mod_string = total_min_mod
                        End If
                    Else
                        total_h_mod_string = "00"
                        total_min_mod_string = "00"
                    End If




                    ' oSheet.Range("G" & Counter_Row.ToString()).Value = "=SUM(G14:G" & (Counter_Row - 1) & ")"
                    SQL.Close()

                    'Write to Second Sheet Header




                    If SQL.State = ConnectionState.Closed Then
                        SQL.Open()
                    End If

                    mySqlCommand.CommandText = "SELECT * FROM Employee JOIN Department where Employee.emp_code = '" & CurEmployee & "' and Employee.dept_id = Department.dept_id ;"

                    mySqlCommand.Connection = SQL
                    mySqlAdaptor.SelectCommand = mySqlCommand
                    mySqlReader = mySqlCommand.ExecuteReader

                    While (mySqlReader.Read())

                        For J = 7 To 10
                            oSheet.Range("I7:N7").Borders(J).Weight = 2 ' xlThin
                        Next
                        For J = 7 To 10
                            oSheet.Range("I8:I8").Borders(J).Weight = 2 ' xlThin
                        Next
                        For J = 7 To 10
                            oSheet.Range("J8:J8").Borders(J).Weight = 2 ' xlThin
                        Next
                        For J = 7 To 10
                            oSheet.Range("K8:K8").Borders(J).Weight = 2 ' xlThin
                        Next
                        For J = 7 To 10
                            oSheet.Range("L8:L8").Borders(J).Weight = 2 ' xlThin
                        Next

                        For J = 7 To 10
                            oSheet.Range("M8:M8").Borders(J).Weight = 2 ' xlThin
                        Next

                        For J = 7 To 10
                            oSheet.Range("N8:N8").Borders(J).Weight = 2 ' xlThin
                        Next



                        oSheet.Range("K7").Value = "Training Group"
                        oSheet.Range("I8").Value = "Course"
                        oSheet.Range("J8").Value = "Trainer"
                        oSheet.Range("K8").Value = "Date"
                        oSheet.Range("L8").Value = "Period"
                        oSheet.Range("M8").Value = "Hrs."
                        oSheet.Range("N8").Value = "Cost"
                    End While

                    SQL.Close()
                    mySqlReader.Close()


                    'Write to second Sheet Content
                    If SQL.State = ConnectionState.Closed Then
                        SQL.Open()
                    End If

                    mySqlCommand.CommandText = "SELECT * FROM Employee JOIN Department JOIN Training JOIN Course where Employee.emp_code = '" & CurEmployee & "' and Employee.dept_id = Department.dept_id and Employee.emp_code = Training.emp_code and Training.course_id = Course.course_id and training.training_type = 'Training Group' and Training.training_date > '" & DateTimePicker1.Value.Year & "-" & DateTimePicker1.Value.Month & "-" & DateTimePicker1.Value.Day & "' and Training.training_date < '" & DateTimePicker2.Value.Year & "-" & DateTimePicker2.Value.Month & "-" & DateTimePicker2.Value.Day & "';"

                    mySqlCommand.Connection = SQL
                    mySqlAdaptor.SelectCommand = mySqlCommand
                    mySqlReader = mySqlCommand.ExecuteReader

                    Counter_Row1 = 9

                    Dim count_money_String_1 As String
                    Dim count_money_int_1 As Integer = 0

                    Dim count_group As Integer = 0
                    Dim string_group(1000) As String
                    string_group(0) = "."
                    While (mySqlReader.Read())
                        oSheet.Range("I" & Counter_Row1.ToString()).Value = mySqlReader("course_name")
                        oSheet.Range("J" & Counter_Row1.ToString()).Value = mySqlReader("course_trainer")
                        oSheet.Range("K" & Counter_Row1.ToString()).Value = mySqlReader("training_date")
                        oSheet.Range("L" & Counter_Row1.ToString()).Value = mySqlReader("time_start") & " - " & mySqlReader("time_end")
                        oSheet.Range("M" & Counter_Row1.ToString()).NumberFormat = "00.00"
                        string_group(count_group) = mySqlReader("training_hours")
                        oSheet.Range("M" & Counter_Row1.ToString()).Value = mySqlReader("training_hours")
                        count_money_String_1 = mySqlReader("training_cost_person")
                        count_money_int_1 += CInt(count_money_String_1)
                        oSheet.Range("N" & Counter_Row1.ToString()).NumberFormat = "#,##0.00"
                        oSheet.Range("N" & Counter_Row1.ToString()).Value = count_money_String_1
                        Counter_Row1 = Counter_Row1 + 1
                        count_group = count_group + 1
                    End While



                    Dim total_h_mod_string_group As String
                    Dim total_min_mod_string_group As String

                    If string_group(0) <> "." Then
                        For J = 0 To count_group - 1
                            Dim split_total() As String
                            split_total = Split(string_group(J), ".")

                            Dim int_h_hr As Integer = CInt(split_total(0))
                            Dim int_h_min As Integer = CInt(split_total(1))

                            total_h = total_h + int_h_hr
                            total_min = total_min + int_h_min

                        Next

                        Dim total_h_mod_group As Integer = total_min \ 60
                        Dim total_min_mod_group As Integer = total_min Mod 60



                        total_h = total_h + total_h_mod_group

                        If (Len(total_h) < 2) Then
                            total_h_mod_string_group = "0" + total_h
                        Else
                            total_h_mod_string_group = total_h
                        End If

                        If (Len(total_min_mod_group) < 2) Then
                            total_min_mod_string_group = "0" + total_min_mod_group
                        Else
                            total_min_mod_string_group = total_min_mod_group
                        End If
                    Else
                        total_h_mod_string_group = "00"
                        total_min_mod_string_group = "00"

                    End If


                    If Counter_Row > Counter_Row1 Then

                        For J = 9 To Counter_Row
                            oSheet.Range("B" & J.ToString() & ":B" & J.ToString()).Borders(7).Weight = 2 ' xlThin
                            oSheet.Range("B" & J.ToString() & ":B" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                            oSheet.Range("C" & J.ToString() & ":C" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                            oSheet.Range("D" & J.ToString() & ":D" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                            oSheet.Range("E" & J.ToString() & ":E" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                            oSheet.Range("F" & J.ToString() & ":F" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                            oSheet.Range("G" & J.ToString() & ":G" & J.ToString()).Borders(10).Weight = 2 ' xlThin


                            oSheet.Range("I" & J.ToString() & ":I" & J.ToString()).Borders(7).Weight = 2 ' xlThin
                            oSheet.Range("I" & J.ToString() & ":I" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                            oSheet.Range("J" & J.ToString() & ":J" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                            oSheet.Range("K" & J.ToString() & ":K" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                            oSheet.Range("L" & J.ToString() & ":L" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                            oSheet.Range("M" & J.ToString() & ":M" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                            oSheet.Range("N" & J.ToString() & ":N" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                        Next

                        Counter_Row = Counter_Row + 1
                        oSheet.Range("B" & Counter_Row.ToString() & ":G" & Counter_Row.ToString()).Borders(8).Weight = 2 ' xlThin
                        oSheet.Range("I" & Counter_Row.ToString() & ":N" & Counter_Row.ToString()).Borders(8).Weight = 2 ' xlThin

                        Counter_Row = Counter_Row + 1


                        oSheet.Range("E" & Counter_Row.ToString()).Value = "Total"
                        oSheet.Range("F" & Counter_Row.ToString()).NumberFormat = "00.00"
                        oSheet.Range("F" & Counter_Row.ToString()).Value = total_h_mod_string + "." + total_min_mod_string
                        oSheet.Range("G" & Counter_Row.ToString()).NumberFormat = "#,##0.00"
                        oSheet.Range("G" & Counter_Row.ToString()).Value = count_money_int.ToString

                        oSheet.Range("L" & Counter_Row.ToString()).Value = "Total"
                        oSheet.Range("M" & Counter_Row.ToString()).NumberFormat = "00.00"
                        oSheet.Range("M" & Counter_Row.ToString()).Value = total_h_mod_string_group + "." + total_min_mod_string_group
                        oSheet.Range("N" & Counter_Row.ToString()).NumberFormat = "#,##0.00"
                        oSheet.Range("N" & Counter_Row.ToString()).Value = count_money_int_1

                        Counter_Row = Counter_Row + 2
                        oSheet.Range("J" & Counter_Row.ToString()).Value = "Summary"
                        Counter_Row += 1
                        oSheet.Range("J" & Counter_Row.ToString()).Value = "Training on the job : "
                        oSheet.Range("K" & Counter_Row.ToString()).NumberFormat = "00.00"
                        oSheet.Range("K" & Counter_Row.ToString()).Value = total_h_mod_string + "." + total_min_mod_string
                        Dim total_hr As Integer = oSheet.Range("K" & Counter_Row.ToString()).Value
                        oSheet.Range("L" & Counter_Row.ToString()).Value = "Hrs."


                        Counter_Row += 1
                        oSheet.Range("J" & Counter_Row.ToString()).Value = "Training group : "
                        oSheet.Range("K" & Counter_Row.ToString()).NumberFormat = "00.00"
                        oSheet.Range("K" & Counter_Row.ToString()).Value = total_h_mod_string_group + "." + total_min_mod_string_group
                        total_hr += oSheet.Range("K" & Counter_Row.ToString()).Value
                        oSheet.Range("L" & Counter_Row.ToString()).Value = "Hrs."

                        Counter_Row += 1
                        oSheet.Range("J" & Counter_Row.ToString()).Value = "Total"
                        oSheet.Range("K" & Counter_Row.ToString()).NumberFormat = "00.00"
                        oSheet.Range("K" & Counter_Row.ToString()).Value = total_hr
                        oSheet.Range("L" & Counter_Row.ToString()).Value = "Hrs."



                    Else
                        For J = 9 To Counter_Row1
                            oSheet.Range("B" & J.ToString() & ":B" & J.ToString()).Borders(7).Weight = 2 ' xlThin
                            oSheet.Range("B" & J.ToString() & ":B" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                            oSheet.Range("C" & J.ToString() & ":C" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                            oSheet.Range("D" & J.ToString() & ":D" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                            oSheet.Range("E" & J.ToString() & ":E" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                            oSheet.Range("F" & J.ToString() & ":F" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                            oSheet.Range("G" & J.ToString() & ":G" & J.ToString()).Borders(10).Weight = 2 ' xlThin


                            oSheet.Range("I" & J.ToString() & ":I" & J.ToString()).Borders(7).Weight = 2 ' xlThin
                            oSheet.Range("I" & J.ToString() & ":I" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                            oSheet.Range("J" & J.ToString() & ":J" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                            oSheet.Range("K" & J.ToString() & ":K" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                            oSheet.Range("L" & J.ToString() & ":L" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                            oSheet.Range("M" & J.ToString() & ":M" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                            oSheet.Range("N" & J.ToString() & ":N" & J.ToString()).Borders(10).Weight = 2 ' xlThin
                        Next
                        Counter_Row1 = Counter_Row1 + 1
                        oSheet.Range("B" & Counter_Row1.ToString() & ":G" & Counter_Row1.ToString()).Borders(8).Weight = 2 ' xlThin
                        oSheet.Range("I" & Counter_Row1.ToString() & ":n" & Counter_Row1.ToString()).Borders(8).Weight = 2 ' xlThin
                        Counter_Row1 = Counter_Row1 + 1
                        oSheet.Range("E" & Counter_Row1.ToString()).Value = "Total"
                        oSheet.Range("F" & Counter_Row1.ToString()).NumberFormat = "00.00"
                        oSheet.Range("F" & Counter_Row1.ToString()).Value = total_h_mod_string + "." + total_min_mod_string
                        oSheet.Range("G" & Counter_Row1.ToString()).NumberFormat = "#,##0.00"
                        oSheet.Range("G" & Counter_Row1.ToString()).Value = count_money_int

                        oSheet.Range("L" & Counter_Row1.ToString()).Value = "Total"
                        oSheet.Range("M" & Counter_Row1.ToString()).NumberFormat = "00.00"
                        oSheet.Range("M" & Counter_Row1.ToString()).Value = total_h_mod_string_group + "." + total_min_mod_string_group
                        oSheet.Range("N" & Counter_Row1.ToString()).NumberFormat = "#,##0.00"
                        oSheet.Range("N" & Counter_Row1.ToString()).Value = count_money_int_1

                        Counter_Row1 = Counter_Row1 + 2
                        oSheet.Range("J" & Counter_Row1.ToString()).Value = "Summary"
                        Counter_Row1 += 1
                        oSheet.Range("J" & Counter_Row1.ToString()).Value = "Training on the job : "

                        oSheet.Range("K" & Counter_Row1.ToString()).NumberFormat = "00.00"
                        oSheet.Range("K" & Counter_Row1.ToString()).Value = total_h_mod_string + "." + total_min_mod_string
                        Dim total_hr1 As Integer = oSheet.Range("K" & Counter_Row1.ToString()).Value
                        oSheet.Range("L" & Counter_Row1.ToString()).Value = "Hrs."

                        Counter_Row1 += 1
                        oSheet.Range("J" & Counter_Row1.ToString()).Value = "Training Group : "

                        oSheet.Range("K" & Counter_Row1.ToString()).NumberFormat = "00.00"
                        oSheet.Range("K" & Counter_Row1.ToString()).Value = total_h_mod_string_group + "." + total_min_mod_string_group
                        total_hr1 += oSheet.Range("K" & Counter_Row1.ToString()).Value
                        oSheet.Range("L" & Counter_Row1.ToString()).Value = "Hrs. "

                        Counter_Row1 += 1
                        oSheet.Range("J" & Counter_Row1.ToString()).Value = "Total"
                        oSheet.Range("K" & Counter_Row1.ToString()).NumberFormat = "00.00"
                        oSheet.Range("K" & Counter_Row1.ToString()).Value = total_hr1
                        oSheet.Range("L" & Counter_Row1.ToString()).Value = "Hrs. "


                    End If
                    ' oSheet.Range("G" & Counter_Row.ToString()).Value = "=SUM(G14:G" & (Counter_Row - 1) & ")"



                    'Save the Workbook and Quit Excel
                    Try
                        oBook.SaveAs(TextBox1.Text + "\" + CurEmployee + ".xlsx")
                        oExcel.DisplayAlerts = False
                    Catch ex As Exception
                        MsgBox("NOT SAVED")
                    End Try


                    oExcel.Quit()

                    Counter_Emp = Counter_Emp + 1
                    SQL.Close()
                    mySqlReader.Close()

                End While
                MsgBox("Save Success", , "Success")
            Else
                MsgBox("NO ITEM SELECTED")
            End If
        End If
    End Sub
    '=========  end comment

    Private Sub ListView1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListView1.SelectedIndexChanged

    End Sub

    Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged

    End Sub


    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet As Object


        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader

        Dim TempString As String
        Dim TempCounter As Integer

        Dim CourseCounter As Integer = 0
        Dim CourseCounter2 As Integer = 0

        respone = MsgBox("Are you sure to generate report", MsgBoxStyle.OkCancel + MsgBoxStyle.Information, "Warning Messsage")
        If respone = 1 Then
            If ListView1.CheckedItems.Count <> 0 Then



                'Start a new workbook in Excel
                oExcel = CreateObject("Excel.Application")
                oBook = oExcel.Workbooks.Add

                'Add data to cells of the first worksheet in the new workbook
                oSheet = oBook.Worksheets(1)
                'oSheet.Range("A1").Value = "Last Name"
                'oSheet.Range("B1").Value = "First Name"
                'oSheet.Range("A1:B1").Font.Bold = True
                'oSheet.Range("A2").Value = "Doe"
                'oSheet.Range("B2").Value = "John"

                oSheet.Range("A:A").ColumnWidth = 14
                oSheet.Range("B:B").ColumnWidth = 14
                oSheet.Range("C:C").ColumnWidth = 14
                oSheet.Range("D:D").ColumnWidth = 14

                '  oSheet.Range("C:C").ColumnWidth = 12
                '  oSheet.Range("A:A").ColumnWidth = 16
                '  oSheet.Range("B:B").ColumnWidth = 12
                '  oSheet.Range("E:E").ColumnWidth = 12


                oSheet.Range("E1").Value = "Training Record"
                oSheet.Range("A3").Value = "Bussiness"
                oSheet.Range("B3").Value = ComboBox1.Text
                oSheet.Range("A4").Value = "Division"
                oSheet.Range("B4").Value = ComboBox2.Text
                oSheet.Range("A5").Value = "Department"

                oSheet.Range("A3:C5").Borders(7).Weight = 2
                oSheet.Range("A3:C5").Borders(8).Weight = 2
                oSheet.Range("A3:C5").Borders(9).Weight = 2
                oSheet.Range("A3:C5").Borders(10).Weight = 2
                oSheet.Range("A3:A5").Font.Color = RGB(255, 255, 255)
                oSheet.Range("A3:A5").Interior.Color = RGB(0, 0, 0)


                oSheet.Range("A7").Value = "From"
                oSheet.Range("B7").Value = DateTimePicker1.Value.Day.ToString() & "/" & DateTimePicker1.Value.Month.ToString() & "/" & DateTimePicker1.Value.Year.ToString()
                oSheet.Range("C7").Value = "To"
                oSheet.Range("D7").Value = DateTimePicker2.Value.Day.ToString() & "/" & DateTimePicker2.Value.Month.ToString() & "/" & DateTimePicker2.Value.Year.ToString()

                oSheet.Range("A7").Font.Color = RGB(255, 255, 255)
                oSheet.Range("A7").Interior.Color = RGB(0, 0, 0)
                oSheet.Range("C7").Font.Color = RGB(255, 255, 255)
                oSheet.Range("C7").Interior.Color = RGB(0, 0, 0)

                TempString = ""
                TempCounter = 0

                While TempCounter < ListView1.CheckedItems.Count
                    TempString = TempString & " " & ListView1.CheckedItems(TempCounter).SubItems(1).Text
                    TempCounter = TempCounter + 1
                End While

                oSheet.Range("B5").Value = TempString

                oSheet.Range("A9").Value = "Course"
                oSheet.Range("B9").Value = "Date"
                oSheet.Range("C9").Value = "Cost"
                oSheet.Range("D9").Value = "Person"
                oSheet.Range("E9").Value = "Cost/Person"

                oSheet.Range("A9:E9").Font.Color = RGB(255, 255, 255)
                oSheet.Range("A9:E9").Interior.Color = RGB(0, 0, 0)


                TempCounter = 0
                TempString = ""

                TempString = "Department.dept_id = '" & ListView1.CheckedItems(0).Text & "'"

                If ListView1.CheckedItems.Count > 1 Then
                    TempCounter = 1
                    While TempCounter < ListView1.CheckedItems.Count
                        TempString = TempString & " or Department.dept_id = '" & ListView1.CheckedItems(TempCounter).Text & "'"
                        TempCounter = TempCounter + 1
                    End While
                End If

                If SQL.State = ConnectionState.Closed Then
                    SQL.Open()
                End If
                Dim ShowCourseID(1000) As String
                CountCourseID = 0
                mySqlCommand.CommandText = "SELECT DISTINCT Course.course_id,Course.course_name,Course.course_start_date,Course.course_cost FROM Employee JOIN Department JOIN Training JOIN Course where Department.dept_id = Employee.dept_id and Training.emp_code=Employee.emp_code and Course.course_id = Training.course_id and ( " & TempString & " ) and course_start_date >= '" & DateTimePicker1.Value.Year & "-" & DateTimePicker1.Value.Month & "-" & DateTimePicker1.Value.Day & "' and course_start_date <= '" & DateTimePicker2.Value.Year & "-" & DateTimePicker2.Value.Month & "-" & DateTimePicker2.Value.Day & "' order by course_start_date asc;"
                'mySqlCommand.CommandText = "SELECT DISTINCT course_name,course_start_date,course_cost FROM Employee JOIN Department JOIN Training JOIN Course where Department.dept_id = Employee.dept_id and Training.emp_code=Employee.emp_code and Course.course_id = Training.course_id and ( " & TempString & " ) ;"
                '' MsgBox(mySqlCommand.CommandText)
                mySqlCommand.Connection = SQL
                mySqlAdaptor.SelectCommand = mySqlCommand
                mySqlReader = mySqlCommand.ExecuteReader
                While (mySqlReader.Read())
                    ShowCourseID(CountCourseID) = mySqlReader("course_id").ToString
                    oSheet.Range("A" & (10 + CourseCounter).ToString()).Value = mySqlReader("course_name")
                    oSheet.Range("B" & (10 + CourseCounter).ToString()).Value = mySqlReader("course_start_date")

                    CourseCounter = CourseCounter + 1
                    CountCourseID = CountCourseID + 1
                End While
                myCountCourseID = CountCourseID

                SQL.Close()
                mySqlReader.Close()

                '====================================
                Dim sumCost As Integer



                CourseCounter2 = 0

                'While CourseCounter2 < CourseCounter
                While CourseCounter2 < myCountCourseID
                    If SQL.State = ConnectionState.Closed Then
                        SQL.Open()
                    End If

                    mySqlCommand.CommandText = "SELECT COUNT(Employee.emp_code) as totalemp,training.training_cost_person from testremote.Employee JOIN testremote.Department JOIN testremote.Training JOIN testremote.Course where (Department.dept_id = Employee.dept_id) and (Employee.emp_code = Training.emp_code) and (Training.course_id = Course.course_id) and Course.course_id = '" & ShowCourseID(CourseCounter2) & "' and (" & TempString & ") Group by Training.training_cost_person;"

                    '' MsgBox(mySqlCommand.CommandText)

                    mySqlCommand.Connection = SQL
                    mySqlAdaptor.SelectCommand = mySqlCommand
                    mySqlReader = mySqlCommand.ExecuteReader

                    While (mySqlReader.Read())
                        Dim countemp As Integer = mySqlReader("totalemp")
                        '' MsgBox(countemp)
                        oSheet.Range("D" & (10 + CourseCounter2).ToString()).Value = countemp
                        Dim costperson As Integer
                        costperson = mySqlReader("training_cost_person")
                        sumCost = countemp * costperson
                        oSheet.Range("E" & (10 + CourseCounter2).ToString()).Value = costperson
                        oSheet.Range("C" & (10 + CourseCounter2).ToString()).Value = sumCost
                    End While


                    SQL.Close()

                    CourseCounter2 = CourseCounter2 + 1
                End While



                oSheet.Range("A" & (10 + CourseCounter2 + 1).ToString()).Value = "TOTAL:"
                oSheet.Range("C" & (10 + CourseCounter2 + 1).ToString()).Value = "=SUM(C8:C" & (CourseCounter2 - 1 + 10).ToString() & ")"


                For index As Integer = 7 To 10
                    oSheet.Range("A9:E" & (10 + CourseCounter2 + 2).ToString()).Borders(index).Weight = 3
                Next

                oSheet.Range("A10:A" & (10 + CourseCounter2 + 2).ToString()).Borders(7).Weight = 2

                '======================================


                'Save the Workbook and Quit Excel
                Try
                    oBook.SaveAs(TextBox1.Text + "\" + ComboBox2.Text + ".xlsx")
                    oExcel.DisplayAlerts = False
                Catch ex As Exception
                    MsgBox("NOT SAVED")
                End Try


                oExcel.Quit()
            End If
        End If
        TempCounter = 0
        myCountCourseID = 0
        CourseCounter = 0
        CourseCounter2 = 0

    End Sub

    Private Sub GroupBox1_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GroupBox1.Enter

    End Sub
End Class